<?php


/**
* Puree CMS
* http://www.molotsoft.ru
*
* @package Puree_CMS
* @subpackage Admin/Components
* @author Ivan Chistyakov <ivan@molotsoft.ru>
* @version $Id: excel_methods.php 1376 2010-04-09 08:36:06Z molotsoft@gmail.com $
* @link $HeadURL: http://puree-cms.googlecode.com/svn/trunk/plugins/admin/controllers/components/excel_methods.php $
*/


if (!defined('TMP_EXCEL')) {
	define('TMP_EXCEL', TMP . 'excel' . DS);
}


class ExcelMethodsComponent extends Object {

/******************************************************************************/

var $Controller = NULL;

var $settings = array();

/******************************************************************************/

var $__mimeType = array(
	'csv' => 'application/csv',
	'html' => 'text/html',
	'pdf' => 'application/pdf',
	'srlz' => 'application/octet-stream',
	'xls' => 'application/vnd.ms-excel',
	'xlsx' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
);

/******************************************************************************/

function initialize(&$Controller, $settings = array()) {
	$this->Controller = &$Controller;
	$this->settings = (array)$settings;
}

/******************************************************************************/

function getExportFields() {
	$ctrl = &$this->Controller;
	$schema = $ctrl->Model->getFieldSchema();
	$fields = array_keys(Set::combine($schema, '{s}.name', '{s}.input.export'), TRUE);
	if (empty($fields)) {
		$fields = array_keys(Set::combine($schema, '{s}.name', '{s}.input.tab'), 'general');
	}
	return $fields;
}

function getImportFields() {
	$ctrl = &$this->Controller;
	$schema = $ctrl->Model->getFieldSchema();
	unset($schema[$ctrl->Model->primaryKey]);
	$fields = array_keys(Set::combine($schema, '{s}.name', '{s}.input.import'), TRUE);
	if (empty($fields)) {
		$fields = array_keys(Set::combine($schema, '{s}.name', '{s}.input.tab'), 'general');
	}
	return $fields;
}

/******************************************************************************/

function rowsToExcel($rows) {
	$ctrl = &$this->Controller;
	App::import('Vendor', 'PHPExcel', array('file' => 'PHPExcel' . DS . 'PHPExcel.php'));
	$phpExcel = new PHPExcel();
	// properties
	$title = Inflector::pluralize($ctrl->Model->name) . date('_Y-m-d_H-i-s');
	$phpExcel->getProperties()->setTitle($title);
	//$phpExcel->getProperties()->setCreated();
	// sheet
	$phpExcel->setActiveSheetIndex(0);
	$phpExcel->getActiveSheet()->setTitle($title);
	if (is_array($rows) && isset($rows[0][$ctrl->Model->name]) && is_array($rows[0][$ctrl->Model->name])) {
		// field names
		$x = 0;
		foreach ($rows[0][$ctrl->Model->name] as $field_name => $field_value) {
			if ($ctrl->Model->hasField($field_name) && is_scalar($field_value)) {
				$phpExcelCell = &$phpExcel->getActiveSheet()->getCellByColumnAndRow($x, 1);
				$phpExcelCell->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);
				$phpExcelCell->setValue($ctrl->Model->name . '.' . $field_name);
				$x ++;
			}
		}
		// rows
		foreach ($rows as $i => $row) {
			if (isset($row[$ctrl->Model->name]) && is_array($row[$ctrl->Model->name])) {
				$x = 0;
				foreach ($row[$ctrl->Model->name] as $field_name => $field_value) {
					if ($ctrl->Model->hasField($field_name) && is_scalar($field_value)) {
						$phpExcelCell = &$phpExcel->getActiveSheet()->getCellByColumnAndRow($x, 2 + $i);
						if (is_numeric($field_value)) {
							$phpExcelCell->setDataType(PHPExcel_Cell_DataType::TYPE_NUMERIC);
						} else {
							$phpExcelCell->setDataType(PHPExcel_Cell_DataType::TYPE_STRING);
						}
						$phpExcelCell->setValue($field_value);
						$x ++;
					}
				}
			}
		}
	}
	return $phpExcel;
}

function excelToRows(&$phpExcel) {
$rows = array();
return $rows;
}

/******************************************************************************/

function saveCSV(&$phpExcel, $filename) {
	App::import('Vendor', 'PHPExcel_Writer_CSV', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'CSV.php'));
	$writer = new PHPExcel_Writer_CSV($phpExcel);
	$writer->save($filename);
	return is_file($filename) && (filesize($filename) > 0);
}

function saveHTML(&$phpExcel, $filename) {
	App::import('Vendor', 'PHPExcel_Writer_HTML', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'HTML.php'));
	$writer = new PHPExcel_Writer_HTML($phpExcel);
	$writer->save($filename);
	return is_file($filename) && (filesize($filename) > 0);
}

function savePDF(&$phpExcel, $filename) {
	App::import('Vendor', 'PHPExcel_Writer_PDF', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'PDF.php'));
	$writer = new PHPExcel_Writer_PDF($phpExcel);
	$writer->save($filename);
	return is_file($filename) && (filesize($filename) > 0);
}

function saveSerialized(&$phpExcel, $filename) {
	App::import('Vendor', 'PHPExcel_Writer_Serialized', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'Serialized.php'));
	$writer = new PHPExcel_Writer_Serialized($phpExcel);
	$writer->save($filename);
	return is_file($filename) && (filesize($filename) > 0);
}

function saveExcel5(&$phpExcel, $filename) {
	App::import('Vendor', 'PHPExcel_Writer_Excel5', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'Excel5.php'));
	$writer = new PHPExcel_Writer_Excel5($phpExcel);
	$writer->save($filename);
	return is_file($filename) && (filesize($filename) > 0);
}

function saveExcel2007(&$phpExcel, $filename) {
	if (class_exists('ZipArchive', FALSE)) {
		App::import('Vendor', 'PHPExcel_Writer_Excel2007', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Writer' . DS . 'Excel2007.php'));
		$writer = new PHPExcel_Writer_Excel2007($phpExcel);
		$writer->save($filename);
		return is_file($filename) && (filesize($filename) > 0);
	}
	return FALSE;
}

/******************************************************************************/

function save(&$phpExcel, $filename, $extension = 'csv') {
	if (isset($this->__mimeType[$extension])) {
		switch ($extension) {
			case 'csv': $this->saveCSV($phpExcel, $filename); break;
			case 'html': $this->saveHTML($phpExcel, $filename); break;
			case 'pdf': $this->savePDF($phpExcel, $filename); break;
			case 'srlz': $this->saveSerialized($phpExcel, $filename); break;
			case 'xls': $this->saveExcel5($phpExcel, $filename); break;
			case 'xlsx': $this->saveExcel2007($phpExcel, $filename); break;
		}
		return is_file($filename) && (filesize($filename) > 0);
	}
	return FALSE;
}

function setDownload(&$phpExcel, $extension = 'csv') {
	$ctrl = &$this->Controller;
	$filename = TMP_EXCEL . uniqid($extension);
	if (isset($this->__mimeType[$extension]) && $this->save($phpExcel, $filename, $extension)) {
		$ctrl->view = 'Media';
		$ctrl->set('mimeType', $this->__mimeType);
		$ctrl->set('path', dirname($filename) . DS);
		$ctrl->set('id', basename($filename));
		$ctrl->set('download', TRUE);
		$ctrl->set('name', Inflector::pluralize($ctrl->Model->name) . date('_Y-m-d_H-i-s'));
		$ctrl->set('extension', $extension);
		return is_file($filename) && (filesize($filename) > 0);
	}
	return FALSE;
}

/******************************************************************************/

function loadCSV($filename) {
	App::import('Vendor', 'PHPExcel_Reader_CSV', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'CSV.php'));
	$reader = new PHPExcel_Reader_CSV();
	if ($reader->canRead($filename)) {
		return $reader->load($filename);
	}
	return FALSE;
}

function loadExcel5($filename) {
	App::import('Vendor', 'PHPExcel_Reader_Excel5', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'Excel5.php'));
	$reader = new PHPExcel_Reader_Excel5();
	if ($reader->canRead($filename)) {
		return $reader->load($filename);
	}
	return FALSE;
}

function loadExcel2003XML($filename) {
	App::import('Vendor', 'PHPExcel_Reader_Excel2003XML', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'Excel2003XML.php'));
	$reader = new PHPExcel_Reader_Excel2003XML();
	if ($reader->canRead($filename)) {
		return $reader->load($filename);
	}
	return FALSE;
}

function loadExcel2007($filename) {
	if (class_exists('ZipArchive', FALSE)) {
		App::import('Vendor', 'PHPExcel_Reader_Excel2007', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'Excel2007.php'));
		$reader = new PHPExcel_Reader_Excel2007();
		if ($reader->canRead($filename)) {
			return $reader->load($filename);
		}
	}
	return FALSE;
}

function loadOOCalc($filename) {
	App::import('Vendor', 'PHPExcel_Reader_OOCalc', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'OOCalc.php'));
	$reader = new PHPExcel_Reader_OOCalc();
	if ($reader->canRead($filename)) {
		return $reader->load($filename);
	}
	return FALSE;
}

function loadSerialized($filename) {
	App::import('Vendor', 'PHPExcel_Reader_Serialized', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'Serialized.php'));
	$reader = new PHPExcel_Reader_Serialized();
	if ($reader->canRead($filename)) {
		return $reader->load($filename);
	}
	return FALSE;
}

function loadSYLK($filename) {
	App::import('Vendor', 'PHPExcel_Reader_SYLK', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'Reader' . DS . 'SYLK.php'));
	$reader = new PHPExcel_Reader_SYLK();
	if ($reader->canRead($filename)) {
		return $reader->load($filename);
	}
	return FALSE;
}

function loadFactory($filename) {
	App::import('Vendor', 'PHPExcel_IOFactory', array('file' => 'PHPExcel' . DS . 'PHPExcel' . DS . 'IOFactory.php'));
	return PHPExcel_IOFactory::load($filename);
}

function load($filename, $extension = FALSE) {
	switch ($extension) {
		case 'csv': return $this->loadCSV($filename); break;
		case 'xls': return $this->loadExcel5($filename); break;
		case 'xml': return $this->loadExcel2003XML($filename); break;
		case 'xlsx': return $this->loadExcel2007($filename); break;
		case 'ods': return $this->loadOOCalc($filename); break;
		case 'srlz': return $this->loadSerialized($filename); break;
		case 'slk': return $this->loadSYLK($filename); break;
		default: return $this->loadFactory($filename); break;
	}
}

/******************************************************************************/

function exportAll() {
	$ctrl = &$this->Controller;
	if (isset($this->__mimeType[$ctrl->params['url']['ext']]) && is_object($ctrl->Model) && $ctrl->Model->isAutomated()) {
		$rows = $ctrl->Model->find('all', array('fields' => $this->getExportFields()));
		$phpExcel = &$this->rowsToExcel($rows);
		if ($this->setDownload($phpExcel, $ctrl->params['url']['ext'])) {
			$ctrl->set('name', Inflector::pluralize($ctrl->Model->name) . '_EXPORT_ALL_' . date('Y-m-d_H-i-s'));
		} else {
			$ctrl->cakeError('error404');
		}
	} else {
		$ctrl->cakeError('error404');
	}
}

function importAll() {
	$ctrl = &$this->Controller;
	if (is_object($ctrl->Model) && $ctrl->Model->isAutomated()) {
//
$ctrl->autoRender = FALSE;
if (isset($ctrl->params['form']['file']['name'], $ctrl->params['form']['file']['tmp_name']) && is_uploaded_file($ctrl->params['form']['file']['tmp_name'])) {
	$phpExcel = @$this->load($ctrl->params['form']['file']['tmp_name'], strtolower(pathinfo($ctrl->params['form']['file']['name'], PATHINFO_EXTENSION)));
	if (is_object($phpExcel)) {
		$rows = $this->excelToRows($phpExcel);
		fb($rows);
	}
}
//
	} else {
		$ctrl->cakeError('error404');
	}
}

/******************************************************************************/

function exportRubric($rubric_id = NULL) {
	$ctrl = &$this->Controller;
	if (isset($this->__mimeType[$ctrl->params['url']['ext']]) && is_object($ctrl->Model) && $ctrl->Model->isAutomated()) {
		$rows = $ctrl->Model->find('all', array('fields' => $this->getExportFields()));
		$phpExcel = &$this->rowsToExcel($rows);
		if ($this->setDownload($phpExcel, $ctrl->params['url']['ext'])) {
			$ctrl->set('name', Inflector::pluralize($ctrl->Model->name) . '_EXPORT_RUBRIC_' . date('Y-m-d_H-i-s'));
		} else {
			$ctrl->cakeError('error404');
		}
	} else {
		$ctrl->cakeError('error404');
	}
}

function importRubric($rubric_id = NULL) {
	$ctrl = &$this->Controller;
	if (is_object($ctrl->Model) && $ctrl->Model->isAutomated()) {
//
	} else {
		$ctrl->cakeError('error404');
	}
}

/******************************************************************************/

function exportFiltered() {
	$ctrl = &$this->Controller;
	if (isset($this->__mimeType[$ctrl->params['url']['ext']]) && is_object($ctrl->Model) && $ctrl->Model->isAutomated()) {
		$rows = $ctrl->Model->find('all', array('fields' => $this->getExportFields()));
		$phpExcel = &$this->rowsToExcel($rows);
		if ($this->setDownload($phpExcel, $ctrl->params['url']['ext'])) {
			$ctrl->set('name', Inflector::pluralize($ctrl->Model->name) . '_EXPORT_FILTERED_' . date('Y-m-d_H-i-s'));
		} else {
			$ctrl->cakeError('error404');
		}
	} else {
		$ctrl->cakeError('error404');
	}
}

function importFiltered() {
	$ctrl = &$this->Controller;
	if (is_object($ctrl->Model) && $ctrl->Model->isAutomated()) {
//
	} else {
		$ctrl->cakeError('error404');
	}
}

/******************************************************************************/

}


?>